package com.xiaopeng.common.jsql;

import com.xiaopeng.common.exception.BusinessException;
import com.xiaopeng.common.jsql.enums.JsqlFieldTypeEnum;
import com.xiaopeng.common.jsql.enums.JsqlLogicEnum;
import com.xiaopeng.common.jsql.enums.JsqlOperationEnum;
import net.sf.jsqlparser.expression.DoubleValue;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.Parenthesis;
import net.sf.jsqlparser.expression.StringValue;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.conditional.OrExpression;
import net.sf.jsqlparser.expression.operators.relational.ComparisonOperator;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
import net.sf.jsqlparser.expression.operators.relational.GreaterThanEquals;
import net.sf.jsqlparser.expression.operators.relational.InExpression;
import net.sf.jsqlparser.expression.operators.relational.IsNullExpression;
import net.sf.jsqlparser.expression.operators.relational.LikeExpression;
import net.sf.jsqlparser.expression.operators.relational.MinorThan;
import net.sf.jsqlparser.expression.operators.relational.MinorThanEquals;
import net.sf.jsqlparser.expression.operators.relational.NotEqualsTo;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.util.deparser.ExpressionDeParser;
import net.sf.jsqlparser.util.deparser.SelectDeParser;
import org.apache.commons.lang3.StringUtils;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.stream.Collectors;

/**
 * JsqlParse工具类 用于解析动态条件项
 * @author caixiaopeng
 * @date 2025/7/21
 */
public class JsqlParseUtil {

    public static void main(String[] args) {

        // 条件项集合
        List<SqlConditionDTO> sqlConditionList = new ArrayList<>();

        SqlConditionDTO sqlConditionDTO_0 = new SqlConditionDTO();
        sqlConditionDTO_0.setLogic(null);   // 括号里第一个关联逻辑不起作用 不用写
        sqlConditionDTO_0.setFieldType(JsqlFieldTypeEnum.STRING.getValue());
        sqlConditionDTO_0.setFieldName("组织机构");
        sqlConditionDTO_0.setOperation(JsqlOperationEnum.EQ.getValue());
        sqlConditionDTO_0.setFieldValue("嘉立创");
        sqlConditionDTO_0.setChildren(null);
        sqlConditionList.add(sqlConditionDTO_0);

        SqlConditionDTO sqlConditionDTO_1 = new SqlConditionDTO();
        sqlConditionDTO_1.setLogic(JsqlLogicEnum.AND.getValue());
        sqlConditionDTO_1.setFieldType(JsqlFieldTypeEnum.STRING.getValue());
        sqlConditionDTO_1.setFieldName("名字");
        sqlConditionDTO_1.setOperation(JsqlOperationEnum.IN.getValue());
        sqlConditionDTO_1.setFieldValue("龙傲天,小帅");
        sqlConditionDTO_1.setChildren(null);
        sqlConditionList.add(sqlConditionDTO_1);

        SqlConditionDTO sqlConditionDTO_2 = new SqlConditionDTO();
        sqlConditionDTO_2.setLogic(JsqlLogicEnum.AND.getValue());
        sqlConditionDTO_2.setFieldType(JsqlFieldTypeEnum.NUMBER.getValue());
        sqlConditionDTO_2.setFieldName("年龄");
        sqlConditionDTO_2.setOperation(JsqlOperationEnum.GE.getValue());
        sqlConditionDTO_2.setFieldValue("20");
        sqlConditionDTO_2.setChildren(null);
        sqlConditionList.add(sqlConditionDTO_2);

        SqlConditionDTO sqlConditionDTO_3 = new SqlConditionDTO();
        sqlConditionDTO_3.setLogic(JsqlLogicEnum.AND.getValue());
        sqlConditionDTO_3.setFieldType(JsqlFieldTypeEnum.GROUP.getValue());
        sqlConditionDTO_3.setFieldName(null);
        sqlConditionDTO_3.setOperation(null);
        sqlConditionDTO_3.setFieldValue(null);
        sqlConditionDTO_3.setChildren(new ArrayList<>());
        sqlConditionList.add(sqlConditionDTO_3);

        SqlConditionDTO sqlConditionDTO_3_1 = new SqlConditionDTO();
        sqlConditionDTO_3_1.setLogic(null);     // 括号里第一个关联逻辑不起作用 不用写
        sqlConditionDTO_3_1.setFieldType(JsqlFieldTypeEnum.NUMBER.getValue());
        sqlConditionDTO_3_1.setFieldName("删除状态");
        sqlConditionDTO_3_1.setOperation(JsqlOperationEnum.EQ.getValue());
        sqlConditionDTO_3_1.setFieldValue("0");
        sqlConditionDTO_3_1.setChildren(null);
        sqlConditionDTO_3.getChildren().add(sqlConditionDTO_3_1);

        SqlConditionDTO sqlConditionDTO_3_2 = new SqlConditionDTO();
        sqlConditionDTO_3_2.setLogic(JsqlLogicEnum.OR.getValue());
        sqlConditionDTO_3_2.setFieldType(JsqlFieldTypeEnum.NUMBER.getValue());
        sqlConditionDTO_3_2.setFieldName("删除状态");
        sqlConditionDTO_3_2.setOperation(JsqlOperationEnum.NULL.getValue());
        sqlConditionDTO_3_2.setFieldValue(null);
        sqlConditionDTO_3_2.setChildren(null);
        sqlConditionDTO_3.getChildren().add(sqlConditionDTO_3_2);

        // 字段名映射
        Map<String, String> fieldNameMap = new HashMap<>();
        fieldNameMap.put("组织机构", "a.organization");
//        fieldNameMap.put("组织机构", "a.organization or 1=1"); // 尝试sql注入
        fieldNameMap.put("名字", "a.name");
        fieldNameMap.put("年龄", "a.age");
        fieldNameMap.put("删除状态", "a.delete_flag");

        String whereSql_1 = parseSqlConditionToWhereSql(sqlConditionList, null);
        System.out.println("字段名映射前-SQL: " + whereSql_1); // (组织机构 = '嘉立创' AND 名字 IN ('龙傲天', '小帅') AND 年龄 >= 20 AND (删除状态 = 0 OR 删除状态 IS NULL))

        String whereSql_2 = parseSqlConditionToWhereSql(sqlConditionList, fieldNameMap);
        System.out.println("字段名映射后-SQL: " + whereSql_2); // (a.organization = '嘉立创' AND a.name IN ('龙傲天', '小帅') AND a.age >= 20 AND (a.delete_flag = 0 OR a.delete_flag IS NULL))
    }


    // 集合进行逗号拼接
    public static String COLLECTION_SPLIT_CHAR = ",";


    /**
     * 解析SqlConditionDTO为where条件语句
     * @param sqlConditionList
     * @return
     */
    public static String parseSqlConditionToWhereSql(List<SqlConditionDTO> sqlConditionList, Map<String, String> fieldNameMap) {
        if (Objects.isNull(sqlConditionList) || sqlConditionList.isEmpty()) {
            return "";
        }
        Expression expression = parseSqlCondition(sqlConditionList, fieldNameMap);
        StringBuilder buffer = new StringBuilder();
        ExpressionDeParser exprDeParser = new ExpressionDeParser(
                new SelectDeParser(), buffer);
        expression.accept(exprDeParser);
        return buffer.toString();
    }

    /**
     * 递归解析SqlConditionDTO
     * @param sqlConditionList  条件项集合
     * @param fieldNameMap      sql字段名映射集合
     * @return
     */
    private static Expression parseSqlCondition(List<SqlConditionDTO> sqlConditionList, Map<String, String> fieldNameMap) {
        Expression where = null;
        for (SqlConditionDTO sqlCondition : sqlConditionList) {
            try {
                Expression right = null;
                if (Objects.equals(sqlCondition.getFieldType(), JsqlFieldTypeEnum.GROUP.getValue())) {
                    // 条件组 -> 递归处理
                    List<SqlConditionDTO> childrenList = sqlCondition.getChildren();
                    if (Objects.isNull(childrenList) || childrenList.isEmpty()) {
                        throw new RuntimeException("条件组内条件项集合为空");
                    }
                    right = parseSqlCondition(childrenList, fieldNameMap);
                } else {
                    // 普通条件
                    String fieldName = sqlCondition.getFieldName();
                    if (Objects.nonNull(fieldNameMap) && fieldNameMap.containsKey(fieldName)) {
                        fieldName = fieldNameMap.get(fieldName);
                    }
                    right = combinationLogic(fieldName, sqlCondition.getFieldType(), sqlCondition.getOperation(), sqlCondition.getFieldValue());
                }
                // 将新生成的条件sql and | or 进已生成where条件
                where = combinationLogic(where, sqlCondition.getLogic(), right);
            } catch (BusinessException e) {
                throw e;
            } catch (Exception e) {
                throw BusinessException.exception(String.format("jsql解析异常 字段：%s 原因：%s", sqlCondition.getFieldName(), e.getMessage()));
            }
        }
        // 封装括号
        return new Parenthesis(where);
    }


    /**
     * 校验表字段名有没有sql注入的风险
     * @param columnName
     * @return
     */
    public static boolean validColumnName(String columnName) {
        if (Objects.isNull(columnName)) {
            throw new RuntimeException("字段名为空");
        }
        try {
            // 尝试解析为合法列名
            String testSQL = "select " + columnName + " from table";
            PlainSelect select = ((PlainSelect) CCJSqlParserUtil.parse(testSQL)).getPlainSelect();
            return true;
        } catch (Exception e) {
            throw new RuntimeException(String.format("sql字段[%s]存在sql注入风险", columnName));
        }
    }


    /**
     * 组装运算条件
     * @param fieldName     字段名
     * @param fieldType     字段类型
     * @param operation     运算操作
     * @param fieldValue    字段值
     * @return
     */
    private static Expression combinationLogic(String fieldName, String fieldType, String operation, String fieldValue) {
        if (Objects.isNull(operation)) {
            throw new RuntimeException("运算条件operation为空");
        }
        // 拼接字段名 并防止列名sql注入
        validColumnName(fieldName);
        Column column = new Column(fieldName);
        // 空/不为空 只需要字段名称 不需要字段值
        if (Arrays.asList(JsqlOperationEnum.NULL.getValue(), JsqlOperationEnum.NOT_NULL.getValue()).contains(operation)) {
            IsNullExpression isNullExpression = new IsNullExpression();
            isNullExpression.setLeftExpression(column);
            isNullExpression.setNot(Objects.equals(operation, JsqlOperationEnum.NOT_NULL.getValue()));
            return isNullExpression;
        } else {
            // 获取字段值表达式 非数字类型使用字符串占位 防止sql注入
            fieldValue = StringUtils.isNotBlank(fieldValue) ? fieldValue : "?";
            Expression fieldExpression = getValueExpression(fieldType, operation, fieldValue);

            // 组装字段名和值
            if (Objects.equals(operation, JsqlOperationEnum.LIKE.getValue())) {
                // 模糊匹配
                LikeExpression likeExpression = new LikeExpression();
                likeExpression.withLeftExpression(column);
                likeExpression.withRightExpression(fieldExpression);
                likeExpression.setNot(false);
                return likeExpression;
            } else if (Arrays.asList(JsqlOperationEnum.IN.getValue(), JsqlOperationEnum.NOT_IN.getValue()).contains(operation)) {
                // 包含、不包含
                InExpression inExpression = new InExpression();
                inExpression.withLeftExpression(column);
                inExpression.withRightExpression(fieldExpression);
                inExpression.setNot(Objects.equals(operation, JsqlOperationEnum.NOT_IN.getValue()));
                return inExpression;
            } else {
                ComparisonOperator comparisonOperator = null;
                if (Objects.equals(operation, JsqlOperationEnum.EQ.getValue())) {
                    // 等于
                    comparisonOperator = new EqualsTo();
                } else if (Objects.equals(operation, JsqlOperationEnum.NOT_EQ.getValue())) {
                    // 不等于
                    comparisonOperator = new NotEqualsTo();
                } else if (Objects.equals(operation, JsqlOperationEnum.LT.getValue())) {
                    // 小于
                    comparisonOperator = new MinorThan();
                } else if (Objects.equals(operation, JsqlOperationEnum.LE.getValue())) {
                    // 小于等于
                    comparisonOperator = new MinorThanEquals();
                } else if (Objects.equals(operation, JsqlOperationEnum.GT.getValue())) {
                    // 大于
                    comparisonOperator = new GreaterThanEquals();
                } else if (Objects.equals(operation, JsqlOperationEnum.GE.getValue())) {
                    // 大于等于
                    comparisonOperator = new GreaterThanEquals();
                } else {
                    throw new RuntimeException("未知的运算条件operation： " + operation);
                }
                comparisonOperator.withLeftExpression(column);
                comparisonOperator.withRightExpression(fieldExpression);
                return comparisonOperator;
            }
        }
    }

    /**
     * 获取value的Expression
     * @param fieldType     字段类型
     * @param operation     运算条件
     * @param fieldValue    字段值
     * @return
     */
    private static Expression getValueExpression(String fieldType, String operation, String fieldValue) {
        // 模糊匹配
        if (Objects.equals(JsqlOperationEnum.LIKE.getValue(), operation)) {
            return new StringValue("%" + fieldValue + "%");
        }

        // 是否集合
        boolean isCollection = Arrays.asList(JsqlOperationEnum.IN.getValue(), JsqlOperationEnum.NOT_IN.getValue()).contains(operation);
        // 数字
        if (Objects.equals(fieldType, JsqlFieldTypeEnum.NUMBER.getValue())) {
            try {
                if (isCollection) {
                    return new Parenthesis(new ExpressionList<>(Arrays.stream(fieldValue.split(COLLECTION_SPLIT_CHAR)).map(DoubleValue::new).collect(Collectors.toList())));
                } else {
                    return new DoubleValue(fieldValue);
                }
            } catch (Exception e) {}  // 数字解析失败用字符串
        }
        // 字符串兜底
        if (isCollection) {
            return new Parenthesis(new ExpressionList<>(Arrays.stream(fieldValue.split(COLLECTION_SPLIT_CHAR)).map(StringValue::new).collect(Collectors.toList())));
        } else {
            return new StringValue(fieldValue);
        }
    }

    /**
     * 组装2个条件项进行 and | or 拼接
     * @param leftExpression    左边条件项
     * @param logic             and | or
     * @param rightExpression   右边条件项
     * @return
     */
    private static Expression combinationLogic(Expression leftExpression, String logic, Expression rightExpression) {
        if (Objects.isNull(leftExpression)) {
            return rightExpression;
        }
        if (Objects.isNull(logic)) {
            throw new RuntimeException("关联条件logic为空");
        }
        if (Objects.equals(logic, JsqlLogicEnum.AND.getValue())) {
            return new AndExpression(leftExpression, rightExpression);
        } else if (Objects.equals(logic, JsqlLogicEnum.OR.getValue())) {
            return new OrExpression(leftExpression, rightExpression);
        } else {
            throw new RuntimeException("未知的关联条件logic： " + logic);
        }
    }

}
